﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using DBUtility;
using MySql.Data.MySqlClient;

namespace DAL
{
    public class DTaxiOnlineTime
    {
        
        public bool Exists(string phonenumber,DateTime date)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from tcaronlinetime");
            strSql.Append(" where PhoneNumber=@PhoneNumber and ");
            strSql.Append("Date=@Date");
            MySqlParameter[] parameters = {
					new MySqlParameter("@PhoneNumber", MySqlDbType.VarChar,20),
                    new MySqlParameter("@Date", MySqlDbType.VarChar,10)
                };
            parameters[0].Value = phonenumber;
            parameters[1].Value = date.Date.ToString("yyyy-MM-dd");
            DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
            if (ds!=null && ds.Tables.Count!=0&&ds.Tables[0].Rows.Count!=0)
            {
                int result = int.Parse(ds.Tables[0].Rows[0][0].ToString());
                return result == 1;
            }
            //return DbHelperMySQL.Exists(strSql.ToString(), parameters);
            return false;
        }
                 
        public bool Add(string phoneNumber,DateTime date,int count)
        {
            int minute = int.Parse(Math.Ceiling((double)count/6).ToString());
            if (!Exists(phoneNumber,date))
            {                
                StringBuilder strSql = new StringBuilder();
                strSql.Append("insert into tcaronlinetime(");
                strSql.Append("PhoneNumber,`Date`,Minutes)");                
                strSql.Append(" values (");
                strSql.Append("@PhoneNumber,@Date,@Minutes)");                
                MySqlParameter[] parameters = {
					new MySqlParameter("@PhoneNumber", MySqlDbType.VarChar,20),
					new MySqlParameter("@Date", MySqlDbType.String,10),					
                    new MySqlParameter("@Minutes", MySqlDbType.Int32)};
                parameters[0].Value = phoneNumber;
                parameters[1].Value = date.Date.ToString("yyyy-MM-dd");
                parameters[2].Value = minute;
                int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
                if (rows > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            else
            {
               return Update(phoneNumber, date, minute);
            }
            
        }

        public bool Update(string phoneNumber, DateTime date, int minute)
        {   // OverlayName,Overlaytype,City,Longitude,Latitude,Star,Remark
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tcaronlinetime set ");
            strSql.Append("Minutes=Minutes+@Minutes ");            
            strSql.Append("where PhoneNumber=@PhoneNumber and ");
            strSql.Append("`Date`=@Date");
            MySqlParameter[] parameters = {
                new MySqlParameter("@PhoneNumber", MySqlDbType.VarChar, 20),
                new MySqlParameter("@Date", MySqlDbType.String, 10), 
                new MySqlParameter("@Minutes", MySqlDbType.Int32)
            };
            parameters[0].Value = phoneNumber;
            parameters[1].Value = date.ToString("yyyy-MM-dd");
            parameters[2].Value = minute;
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public DataSet GetListBetween(string startDate,string endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select PhoneNumber,Date,Minutes");
            strSql.Append(" from tcaronlinetime ");
            strSql.Append("where STR_TO_DATE(Date,'%Y-%m-%d') ");
            strSql.Append("Between '");
            strSql.Append(startDate);
            strSql.Append("' and '");
            strSql.Append(endDate);
            strSql.Append("'");
            return DbHelperMySQL.Query(strSql.ToString());
        }
        
        public DataSet GetList(string date1,string date2,string parameter, string parameterVaule)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select t.PhoneNumber,t.Date,");
            strSql.Append("(Sum(t.Minutes)/60)as Minutes,");
            strSql.Append("tdr.Name,ttaxi.TaxiNo,ttaxi.Remark,");
            strSql.Append("tgroup.TaxiGroup,tcompany.CompanyName FROM ");
            strSql.Append("tcaronlinetime t ");
            strSql.Append("left join tdriver tdr ");
            strSql.Append("on t.PhoneNumber = tdr.PhoneNumber ");
            strSql.Append("left join ttaxi as ttaxi ");
            strSql.Append("on tdr.TaxiID= ttaxi.id ");
            strSql.Append("left join ttaxigroup as tgroup ");
            strSql.Append("on ttaxi.TaxiGroupId = tgroup.id ");

            strSql.Append("left join tcompany on ttaxi.CompanyId=");
            strSql.Append("tcompany.Id ");

            strSql.Append("where STR_TO_DATE(Date,'%Y-%m-%d') ");
            strSql.Append("Between '");
            strSql.Append(date1);
            strSql.Append("' and '");
            strSql.Append(date2);
            strSql.Append("'");
           // strSql.Append(" and ttaxi.Remark is not null");
            //if (parameter != "" && parameterVaule != "")
            if ( parameterVaule != "")
            {
                if (parameter!="")
                {
                    strSql.Append(" and ");
                    strSql.Append(parameter);
                    strSql.Append("='");    
                }                
                strSql.Append(parameterVaule);
                strSql.Append("'");
            }
            switch (parameter)
            {
                case "t.PhoneNumber"://某辆车辆  按日期区分
                    strSql.Append(" group by Date");
                    break;

                //多辆车辆     按车牌号区分
                case "":
                    strSql.Append(" group by Date,PhoneNumber");
                    break;
                case "tgroup.id":
                    strSql.Append(" group by Date,PhoneNumber");
                    break;
                case "CompanyName":
                    strSql.Append(" group by Date,PhoneNumber");
                    break;
            }
            strSql.Append(" Order by PhoneNumber,Date asc");
            return DbHelperMySQL.Query(strSql.ToString());
        }


        public DataSet GetListWhere(string date1, string date2, string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select t.PhoneNumber,t.Date,");
            strSql.Append("(Sum(t.Minutes)/60)as Minutes,");
            strSql.Append("tdr.Name,ttaxi.TaxiNo,ttaxi.Remark,");
            strSql.Append("tgroup.TaxiGroup,tcompany.CompanyName FROM ");
            strSql.Append("tcaronlinetime t ");
            strSql.Append("left join tdriver tdr ");
            strSql.Append("on t.PhoneNumber = tdr.PhoneNumber ");
            strSql.Append("left join ttaxi as ttaxi ");
            strSql.Append("on tdr.TaxiID= ttaxi.id ");
            strSql.Append("left join ttaxigroup as tgroup ");
            strSql.Append("on ttaxi.TaxiGroupId = tgroup.id ");

            strSql.Append("left join tcompany on ttaxi.CompanyId=");
            strSql.Append("tcompany.Id ");

            strSql.Append("where STR_TO_DATE(Date,'%Y-%m-%d') ");
            strSql.Append("Between '");
            strSql.Append(date1);
            strSql.Append("' and '");
            strSql.Append(date2);
            strSql.Append("' ");
            if (strWhere.Trim()!="")
            {
                strSql.Append(strWhere);
            }
            strSql.Append(" group by Date,PhoneNumber");
            strSql.Append(" Order by PhoneNumber,Date asc");
            return DbHelperMySQL.Query(strSql.ToString());
        }


 
     }
}
